﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Oracle.DataAccess.Client;

namespace Petaframe.Generation
{
    public class OracleDataProvider : IDataProvider
    {

        #region IDataProvider Members

        public string ConnectionString { get; set; }
        public string TableName { get; set; }
        public string PrimaryKeyColumnName { get; set; }
        public string EnumColumnName { get; set; }
        public string PrimaryKeyColumnsAsParameter { get; set; }

        public DataTable TabloBilgileriniAl()
        {


            string sqlText = "Select  COLUMN_NAME,DATA_TYPE DB_TYPE,case DATA_TYPE when  'NUMBER'  THEN 'decimal' WHEN 'BFILE' THEN 'byte[]' WHEN 'CHAR' THEN ( CASE u.DATA_LENGTH WHEN 1 THEN 'char' ELSE 'string' END) WHEN 'CLOB' THEN 'string' WHEN 'VARCHAR2' THEN ( CASE u.DATA_LENGTH WHEN 1 THEN 'char' ELSE 'string' END) WHEN 'BLOB' THEN 'byte[]' WHEN 'CHAR' THEN 'string' WHEN 'DATE' THEN 'DateTime' WHEN 'FLOAT' THEN 'decimal' WHEN 'INT' THEN 'decimal' WHEN 'LONG' THEN 'string' WHEN 'LONG RAW' THEN 'byte[]' WHEN 'NCHAR' THEN ( CASE u.DATA_LENGTH WHEN 1 THEN 'char' ELSE 'string' END) WHEN 'NCLOB' THEN 'string' WHEN 'NVARCHAR2' THEN ( CASE u.DATA_LENGTH WHEN 1 THEN 'char' ELSE 'string' END) WHEN 'RAW' THEN 'byte[]'  WHEN 'REAL' THEN 'decimal'  WHEN 'ROWID' THEN 'string' WHEN 'TIMESTAMP' THEN 'DateTime' WHEN  'TIMESTAMP(0)' THEN 'DateTime' WHEN  'TIMESTAMP(1)' THEN 'DateTime' WHEN  'TIMESTAMP(2)' THEN 'DateTime' WHEN  'TIMESTAMP(3)' THEN 'DateTime' WHEN  'TIMESTAMP(4)' THEN 'DateTime' WHEN  'TIMESTAMP(5)' THEN 'DateTime' WHEN  'TIMESTAMP(6)' THEN 'DateTime' WHEN  'TIMESTAMP(7)' THEN 'DateTime' WHEN  'TIMESTAMP(0) WITH TIME ZONE' THEN 'string'    WHEN  'TIMESTAMP(1) WITH TIME ZONE' THEN 'string' WHEN  'TIMESTAMP(2) WITH TIME ZONE' THEN 'string' WHEN  'TIMESTAMP(3) WITH TIME ZONE' THEN 'string' WHEN  'TIMESTAMP(4) WITH TIME ZONE' THEN 'string' WHEN  'TIMESTAMP(5) WITH TIME ZONE' THEN 'string' WHEN  'TIMESTAMP(6) WITH TIME ZONE' THEN 'string' WHEN  'TIMESTAMP(7) WITH TIME ZONE' THEN 'string' WHEN  'TIMESTAMP(8) WITH TIME ZONE' THEN 'string' WHEN  'TIMESTAMP(9) WITH TIME ZONE' THEN 'string'  WHEN  'TIMESTAMP(0) WITH LOCAL TIME ZONE' THEN 'string' WHEN  'TIMESTAMP(1) WITH LOCAL TIME ZONE' THEN 'string'  WHEN  'TIMESTAMP(2) WITH LOCAL TIME ZONE' THEN 'string' WHEN  'TIMESTAMP(3) WITH LOCAL TIME ZONE' THEN 'string' WHEN  'TIMESTAMP(4) WITH LOCAL TIME ZONE' THEN 'string' WHEN  'TIMESTAMP(5) WITH LOCAL TIME ZONE' THEN 'string' WHEN  'TIMESTAMP(6) WITH LOCAL TIME ZONE' THEN 'string'  WHEN  'TIMESTAMP(7) WITH LOCAL TIME ZONE' THEN 'string'  WHEN  'TIMESTAMP(8) WITH LOCAL TIME ZONE' THEN 'string'  WHEN  'TIMESTAMP(9) WITH LOCAL TIME ZONE' THEN 'string' WHEN 'UROWID' THEN 'string' ELSE 'object' end CODE_TYPE  , (SELECT  cols.column_name FROM all_constraints cons, all_cons_columns cols WHERE cons.constraint_type = 'P' AND cons.constraint_name = cols.constraint_name AND cons.owner = cols.owner AND COLS.TABLE_NAME=U.TABLE_NAME and  cols.column_name=u.column_name) PRIMARY_KEY_COLUMN,'' IDENTITY_KEY_COLUMN  from user_tab_columns  u where table_name=:TABLENAME";

            DataTable dt = new DataTable();
            OracleConnection OraCon = null;

            try
            {
                OraCon = new OracleConnection(this.ConnectionString);
                OraCon.Open();

                OracleCommand OraCom = new OracleCommand(sqlText, OraCon);
                OracleParameter param = new OracleParameter();
                param.DbType = DbType.String;
                param.ParameterName = "TABLENAME";
                param.Value = this.TableName;
                OraCom.Parameters.Add(param);
                OracleDataAdapter adapter = new OracleDataAdapter(OraCom);
                adapter.Fill(dt);

                this.PrimaryKeyKolonBelirle(dt);
            }
            catch (Exception exc)
            {
                throw exc;
            }
            finally
            {
                OraCon.Close();
            }

            return dt;


          
        }

        public DataTable TabloEnumBilgisiniAl()
        {
            string sqlText = @"SELECT * FROM " + this.TableName;

            DataTable dt = new DataTable();
            OracleConnection OraCon = null;

            try
            {
                OraCon = new OracleConnection(this.ConnectionString);
                OraCon.Open();

                OracleCommand OraCom = new OracleCommand(sqlText, OraCon);
                OracleDataAdapter adapter = new OracleDataAdapter(OraCom);
                adapter.Fill(dt);

                foreach (DataColumn column in dt.Columns)
                {
                    if (column.ColumnName != this.PrimaryKeyColumnName)
                    {
                        this.EnumColumnName = column.ColumnName;
                        break;
                    }
                }

            }
            catch (Exception exc)
            {
                throw exc;
            }
            finally
            {
                OraCon.Close();
            }

            return dt;
        }

        public void PrimaryKeyKolonBelirle(DataTable dt)
        {
            this.PrimaryKeyColumnName = String.Empty;
            this.PrimaryKeyColumnsAsParameter = String.Empty;

            foreach (DataRow row in dt.Rows)
            {
                if (row["COLUMN_NAME"].ToString() == row["PRIMARY_KEY_COLUMN"].ToString())
                {
                    if (!String.IsNullOrEmpty(this.PrimaryKeyColumnName))
                    {
                        this.PrimaryKeyColumnName += ",";
                        this.PrimaryKeyColumnsAsParameter += ",";
                    }

                    this.PrimaryKeyColumnName += row["COLUMN_NAME"].ToString();
                    this.PrimaryKeyColumnsAsParameter += row["CODE_TYPE"].ToString() + " " + row["COLUMN_NAME"].ToString();
                }
            }
        }

        public DataTable TablolariYukle()
        {
            //DATA SOURCE=XE;PERSIST SECURITY INFO=False;USER ID=SOUTHWIN;PASSWORD=S123"

            string sqlText = @"SELECT TABLE_NAME  FROM CAT WHERE TABLE_TYPE='TABLE'";

            DataTable dt = new DataTable();

            OracleConnection con = null;
            try
            {
                con = new OracleConnection(this.ConnectionString);
               
                OracleCommand cmd = new OracleCommand(sqlText, con);


                OracleDataAdapter adapter = new OracleDataAdapter(cmd);
                adapter.Fill(dt);
            }
            catch (Exception exc)
            {
                throw exc;
            }
            finally
            {
                con.Close();
            }

            return dt;
        }

        #endregion
    }
}
